package com.rain.builder;

import com.rain.bean.FieldInfo;
import com.rain.utils.Constants;
import com.rain.bean.TableInfo;
import com.rain.utils.JsonUtils;
import com.rain.utils.PropertiesUtils;
import com.rain.utils.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class BuildTable {

    private static final Logger logger = LoggerFactory.getLogger(BuildTable.class);
    private static Connection connection = null;

    // 看数据库有多少表 + 表信息
    private static String SQL_SHOW_TABLE_STATUS = "show table status";

    // 查看表字段信息
    private static String SQL_SHOW_TABLE_FIELDS = "show full fields from %s";

    // 查看索引信息
    private static String SQL_SHOW_TABLE_INDEX = "show index from %s";

    static {
        String driverName = PropertiesUtils.getString("db.driver.name");
        String url = PropertiesUtils.getString("db.url");
        String username = PropertiesUtils.getString("db.username");
        String password = PropertiesUtils.getString("db.password");
        try {
            Class.forName(driverName);
            connection = DriverManager.getConnection(url,username,password);
        } catch (Exception e) {
            logger.error("数据库连接失败， " + e.getMessage());
            e.printStackTrace();
        }
    }


    /**
     * 获取表信息
     */
    public static List<TableInfo> getTables() throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        List<TableInfo> tableInfoList = new ArrayList<>();
        try {
            preparedStatement = connection.prepareStatement(SQL_SHOW_TABLE_STATUS);
            resultSet = preparedStatement.executeQuery();


            while (resultSet.next()){
                String tableName = resultSet.getString("name"); // 表名
                String tableComment = resultSet.getString("comment"); // 表备注
                // logger.info("tableName:{},tableComment:{}",tableName,tableComment);

                // 去除表名前缀
                String beanName = tableName;
                if(Constants.IGNORE_TABLE_PERFIX){
                    beanName = tableName.substring(beanName.indexOf("_") +1);
                }
                beanName = processFiled(beanName,true);

                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(tableComment);
                tableInfo.setBeanParamName(beanName + Constants.SUFFIX_BEAN_PARAM);

                readFieldInfo(tableInfo);

                getKeyIndexInfo(tableInfo);

                tableInfoList.add(tableInfo);

                //logger.info("表：{}",JsonUtils.coverObj2Json(tableInfo));


            }


        }catch (Exception e) {
            logger.error("读取表失败， " + e.getMessage());
            e.printStackTrace();
        }finally {
            if (resultSet != null){
                resultSet.close();
            }
            if (preparedStatement != null){
                preparedStatement.close();
            }
            if (connection != null){
                connection.close();
            }
        }

        return tableInfoList;
    }


    /**
     * 获取字段信息
     */
    public static void readFieldInfo(TableInfo tableInfo) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(String.format(SQL_SHOW_TABLE_FIELDS,tableInfo.getTableName()));
            resultSet = preparedStatement.executeQuery();

            List<FieldInfo> fieldInfoList = new ArrayList<>();
            List<FieldInfo> filedExtendList = new ArrayList<>();

            Boolean haveDateTime = false;
            Boolean haveDate = false;
            Boolean haveBigDecimal = false;

            while (resultSet.next()){
                String fieldName = resultSet.getString("field"); // 字段信息
                String fieldType = resultSet.getString("type"); // 字段类型
                String fieldComment = resultSet.getString("comment"); // 字段备注
                String fieldExtra = resultSet.getString("extra"); // 字段类型
                if(fieldType.indexOf("(") > 0){
                    fieldType = fieldType.substring(0,fieldType.indexOf("("));
                }
                String propertyName = processFiled(fieldName,false);
                //logger.info("fieldName:{},fieldType:{},fieldComment:{},fieldExtra:{}",fieldName,fieldType,fieldComment,fieldExtra);

                FieldInfo fieldInfo = new FieldInfo();
                fieldInfoList.add(fieldInfo);

                fieldInfo.setFieldName(fieldName);
                fieldInfo.setComment(fieldComment);
                fieldInfo.setSqlType(fieldType);
                fieldInfo.setAutoIncrement("auto_increment".equalsIgnoreCase(fieldExtra) ? true : false);
                fieldInfo.setPropertyName(propertyName);
                fieldInfo.setJavaType(processJavaType(fieldType));
                if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES,fieldType) || ArrayUtils.contains(Constants.SQL_DATE_TYPES,fieldType)) {
                    haveDateTime = true;
                }
                if(ArrayUtils.contains(Constants.SQL_DATE_TYPES,fieldType)) {
                    haveDate = true;
                }
                if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE,fieldType)) {
                    haveBigDecimal = true;
                }

                // String 类型的参数 添加Query字段
                if(ArrayUtils.contains(Constants.SQL_STAING_TYPE,fieldType)){
                    FieldInfo fuzzy = new FieldInfo();
                    fuzzy.setJavaType(fieldInfo.getJavaType());
                    fuzzy.setPropertyName(propertyName + Constants.QUERY_FUZZY);
                    fuzzy.setFieldName(fieldInfo.getFieldName());
                    fuzzy.setSqlType(fieldType);
                    filedExtendList.add(fuzzy);
                }

                // Date 类型的参数 添加Query字段
                if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES,fieldType) || ArrayUtils.contains(Constants.SQL_DATE_TYPES,fieldType)){
                    String properNameStart = fieldInfo.getPropertyName() + Constants.QUERY_TIME_START;
                    String properNameEnd = fieldInfo.getPropertyName() + Constants.QUERY_TIME_END;
                    FieldInfo start = new FieldInfo();
                    start.setJavaType("String");
                    start.setPropertyName(properNameStart);
                    start.setFieldName(fieldInfo.getFieldName());
                    start.setSqlType(fieldType);
                    filedExtendList.add(start);


                    FieldInfo end = new FieldInfo();
                    end.setJavaType("String");
                    end.setPropertyName(properNameEnd);
                    end.setSqlType(fieldType);
                    end.setFieldName(fieldInfo.getFieldName());
                    filedExtendList.add(end);
                }

                tableInfo.setHaveDate(haveDate);
                tableInfo.setHaveDateTime(haveDateTime);
                tableInfo.setHaveBigDecimal(haveBigDecimal);
                tableInfo.setFieldList(fieldInfoList);
                tableInfo.setFieldExtendList(filedExtendList);



                //logger.info(fieldInfo.toString());
            }
        }catch (Exception e) {
            logger.error("读取字段失败， " + e.getMessage());
            e.printStackTrace();
        }finally {
            if (resultSet != null){
                resultSet.close();
            }
            if (preparedStatement != null){
                preparedStatement.close();
            }

        }
    }


    /**
     * 获取索引信息
     */
    public static List<FieldInfo> getKeyIndexInfo(TableInfo tableInfo) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(String.format(SQL_SHOW_TABLE_INDEX,tableInfo.getTableName()));
            resultSet = preparedStatement.executeQuery();

            Map<String, FieldInfo> tempMap = new HashMap<>();
            for (FieldInfo fieldInfo: tableInfo.getFieldList()) {
                tempMap.put(fieldInfo.getFieldName(),fieldInfo);
            }

            while (resultSet.next()){
                String keyName = resultSet.getString("key_name"); // key名字
                Integer nonUnique = resultSet.getInt("non_unique"); // 字段类型
                String columnName = resultSet.getString("column_name"); // 字段备注
                if(nonUnique == 1){
                    continue;
                }
                List<FieldInfo> fieldInfoList1 = tableInfo.getKeyIndexMap().get(keyName);
                if(fieldInfoList1 == null) {
                    fieldInfoList1 = new ArrayList<>();
                    tableInfo.getKeyIndexMap().put(keyName,fieldInfoList1);
                }
//                for (FieldInfo fieldInfo : tableInfo.getFieldList()){
//                    if (fieldInfo.getFieldName().equals(columnName)){
//                        fieldInfoList1.add(fieldInfo);
//                    }
//                }
                fieldInfoList1.add(tempMap.get(columnName));
                //logger.info(fieldInfo.toString());
            }
        }catch (Exception e) {
            logger.error("读取索引失败， " + e.getMessage());
            e.printStackTrace();
        }finally {
            if (resultSet != null){
                resultSet.close();
            }
            if (preparedStatement != null){
                preparedStatement.close();
            }

        }
        return null;
    }

    /**
     * 表名转换驼峰
     */
    private static String processFiled(String filed,Boolean uperCaseFirstLetter) {
        StringBuffer stringBuffer = new StringBuffer();
        String[] fields = filed.split("_");
        stringBuffer.append(uperCaseFirstLetter?StringUtils.uperCaseFirstLetter(fields[0]):fields[0]);
        for (int i=1, len = fields.length; i< len;i++) {
            stringBuffer.append(StringUtils.uperCaseFirstLetter(fields[i]));
        }
        return stringBuffer.toString();
    }

    /**
     * java类型转换
     */
    private static String processJavaType(String type){

        if (ArrayUtils.contains(Constants.SQL_INTEGER_TYPE,type)){
            return "Integer";
        }else if (ArrayUtils.contains(Constants.SQl_LONG_TYPE,type)){
            return "Long";
        }else if (ArrayUtils.contains(Constants.SQL_STAING_TYPE,type)){
            return "String";
        }else if (ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES,type) || ArrayUtils.contains(Constants.SQL_DATE_TYPES,type)){
            return "Date";
        }else if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE,type)){
            return "BigDecimal";
        }else {
            throw new RuntimeException("无法识别类型" + type);
        }
    }



}
